package JDBC;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import Model.Eleve;
import Model.Famille;
import Model.Lieu;

public class Eleve_Afficher
{
	
	public ArrayList<Eleve> afficher() 
	{
		ArrayList<Eleve> mesEleves = new ArrayList<Eleve>();
		
		try 
		{
			connexionBDD a = new connexionBDD();
			Connection conn=a.connect();

			//Création d'un objet Statement
			Statement state = conn.createStatement();
			Statement state2 = conn.createStatement();
			Statement state3 = conn.createStatement();
			
			//L'objet ResultSet contient le résultat de la requête SQL
			ResultSet result = state.executeQuery("SELECT * FROM eleve ORDER BY nom_eleve");
					
			while(result.next())
			{	
				Eleve eleve = new Eleve();
				Lieu ad = new Lieu();
				Famille f = new Famille();
				
				eleve.setIdEleve(Integer.parseInt(result.getObject(1).toString()));
				eleve.setNom(result.getObject(2).toString());
				eleve.setPrenom(result.getObject(3).toString());
				eleve.setSexe(result.getObject(4).toString());
				eleve.setNaissance(result.getObject(5).toString());
				eleve.setEmail(result.getObject(6).toString());
				eleve.setTel(result.getObject(7).toString());
						
				ResultSet result2 = state2.executeQuery("SELECT * FROM lieu where id_lieu="+Integer.parseInt(result.getObject(8).toString()));

				while(result2.next()){
					ad.setIdLieu(Integer.parseInt(result2.getObject(1).toString()));
					ad.setNumero(Integer.parseInt(result2.getObject(2).toString()));
					ad.setRue(result2.getObject(3).toString());
					ad.setComplement(result2.getObject(4).toString());
					ad.setCp(Integer.parseInt(result2.getObject(5).toString()));
					ad.setVille(result2.getObject(6).toString());
					eleve.setAdresse_cours(ad);
				}
				
				ResultSet result3 = state3.executeQuery("SELECT * FROM famille where id_famille="+Integer.parseInt(result.getObject(9).toString()));
				int i=1;
				while(result3.next()){
					System.out.println(".."+result3.getObject(i).toString()+"..");
					f.setIdFamille(Integer.parseInt(result3.getObject(1).toString()));
					f.setNom(result3.getObject(2).toString());
					f.setPrenom(result3.getObject(3).toString());
					f.setNum(Integer.parseInt(result3.getObject(4).toString()));
					f.setMail(result3.getObject(5).toString());
					eleve.setId_famille(f);
					i++;
				}
				
				mesEleves.add(eleve);
				eleve.toString();
				result2.close();
				result3.close();
				
			}
			
			result.close();
			state.close();
			state2.close();
		} 

		catch (Exception e) 
		{
			e.printStackTrace();
		}	
		return mesEleves;

	}

}
